{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 独立使用django model（一）\n",
    "# Django Shell\n",
    "# 项目根目录中打开Django Shell : python manage.py shell\n",
    "# import django\n",
    "# django.setup()  # 装载Django\n",
    "\n",
    "# 独立使用django model（二）\n",
    "# python ×××.py or Jupyter Notebook\n",
    "# 将脚本放置在项目根目录下jupyter_notebook文件中比较方便\n",
    "import sys\n",
    "import os\n",
    "import json\n",
    "import requests\n",
    "from django.core.files import File\n",
    "from django.core.files.base import ContentFile\n",
    "# 外部脚本链接django项目\n",
    "# 添加环境变量\n",
    "'''\n",
    "print(os.path.abspath('__file__'))\n",
    "print(os.path.dirname(os.path.abspath('__file__')))\n",
    "print(os.path.dirname(os.path.dirname(os.path.abspath('__file__'))))\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook/__file__\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis\n",
    "'''\n",
    "project = os.path.dirname(os.getcwd())  # get current work directory\n",
    "sys.path.append(project)\n",
    "sys.path.append(os.path.join(project, 'rmis'))\n",
    "# sys.path.append已设置临时环境变量，可以直接调用其中的文件，脚本是外部脚本，只是放在了项目当中而已\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')\n",
    "# 相关数据库配置等都在settings.py文件中\n",
    "# 导入并装载django\n",
    "import django\n",
    "django.setup()\n",
    "# 脚本正文"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------\n",
      "标签系列：版式\n",
      "----------\n",
      "标签：修身\n",
      "标签：宽松\n",
      "标签：紧身\n",
      "----------\n",
      "标签系列：风格\n",
      "----------\n",
      "标签：中性\n",
      "标签：可爱\n",
      "标签：成熟\n",
      "[{'name': '版式', 'y': 4076, 'drilldwon': '版式'}, {'name': '风格', 'y': 4076, 'drilldwon': '风格'}]\n",
      "[{'id': '版式', 'data': [{'name': '修身', 'y': 395871}, {'name': '宽松', 'y': 285166}, {'name': '紧身', 'y': 230187}]}, {'id': '风格', 'data': [{'name': '中性', 'y': 285701}, {'name': '可爱', 'y': 350471}, {'name': '成熟', 'y': 275052}]}]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['版式', '风格']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from django.forms import model_to_dict\n",
    "from invoice.models import Sale,GoodInSale\n",
    "from label.models import LabelTarget, LabelSeries, Label, LabelStickGood\n",
    "\n",
    "# 商品 标签 系列\n",
    "series =[ s for s in LabelTarget.objects.get(target=\"good\").labelseries_set.all() ] \n",
    "\n",
    "# 某一系列标签\n",
    "series = [ model_to_dict(s) for s in LabelSeries.objects.filter(fk_target__target=\"good\") ] \n",
    "labels = [ model_to_dict(label) for label in Label.objects.filter() ]\n",
    "links = [ model_to_dict(link) for link in LabelStickGood.objects.filter() ]\n",
    "df_label_series = pd.DataFrame.from_dict(series)\n",
    "df_label = pd.DataFrame.from_dict(labels)\n",
    "df_link = pd.DataFrame.from_dict(links)\n",
    "\n",
    "df_label_series = df_label_series.set_index(\"id\")\n",
    "df_label = df_label.join(df_label_series, on=\"fk_series\",lsuffix='_of_label', rsuffix='_of_label_series')\n",
    "# df_label = df_label.set_index()\n",
    "df_label = df_label.set_index(\"id\")\n",
    "df_link = df_link.join(df_label,on=\"fk_label\",lsuffix='_of_good', rsuffix='_of_label')\n",
    "df_link\n",
    "# 已联系商品和标签信息\n",
    "\n",
    "goodsinsale = [ model_to_dict(good) for good in GoodInSale.objects.filter() ]\n",
    "df_goodinsale = pd.DataFrame.from_records(goodsinsale)\n",
    "# df_link  fk_good\n",
    "# df_goodinsale fk_good\n",
    "df = pd.merge(df_goodinsale, df_link, how='left', on=['fk_good'], suffixes=('_goodinsale', '_link'), )\n",
    "# \"amount\", \"created\", \"fk_good\", \"interests\", \"quantity\", \"title_of_label\", \"title_of_label_series\" \n",
    "\n",
    "# 已经使用了的\n",
    "series_data = []\n",
    "drilldown_series = []\n",
    "for series_name,series_group in df.groupby(\"title_of_label_series\"):\n",
    "    series_data.append(\n",
    "        {\n",
    "            \"name\":series_name,\n",
    "            \"y\":series_group.count()[\"title_of_label_series\"],\n",
    "            \"drilldwon\":series_name,\n",
    "        }\n",
    "    )\n",
    "    print(\"----------\\n标签系列：{0}\\n----------\".format(series_name))\n",
    "    drilldown_series_data = list()\n",
    "    for label_name,label_group in series_group.groupby(\"title_of_label\"):\n",
    "        drilldown_series_data.append(\n",
    "            {\n",
    "                \"name\":label_name,\n",
    "                \"y\":label_group[\"amount\"].sum()\n",
    "            }\n",
    "        )\n",
    "        print(\"标签：{0}\".format(label_name))\n",
    "    drilldown_series.append({\n",
    "        \"type\": 'pie',\n",
    "        \"id\":series_name,\n",
    "        \"data\":drilldown_series_data\n",
    "    })\n",
    "        \n",
    "print(series_data)\n",
    "print(drilldown_series)\n",
    "\n",
    "df.groupby(\"title_of_label_series\").get_group(\"版式\")\n",
    "df.groupby(\"title_of_label_series\").aggregate(np.sum) \n",
    "df.groupby(\"title_of_label_series\", as_index=False).sum()\n",
    "[ name for name,group in df.groupby(\"title_of_label_series\") ]\n",
    "\n",
    "# df.count()[\"amount\"]\n",
    "# df.sum()[\"amount\"]\n",
    "# df[\"amount\"].sum()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
